package dao;

import java.sql.Connection;
import java.sql.PreparedStatement;
import java.sql.ResultSet;
import java.sql.SQLException;
import java.util.ArrayList;
import java.util.Date;
import java.util.HashMap;
import java.util.List;
import java.util.Map;

import org.apache.commons.lang3.StringUtils;

import com.mysql.jdbc.Statement;

import dbutil.DBHelper;
import entity.User;
import entity.UserActRecord;
import entity.UserActiveSoft;
import entity.UserLicense;
import entity.UserLicenseReq;


public class UserDao {
	
	private boolean isSuccess;
	

	// 添加用户
	public boolean createUser(User user) throws SQLException {
		Connection conn = null;
		String sql = "insert into users(userName,password,cnName,category,deptName,email,status,regTime,adminName) values(?,?,?,?,?,?,?,?,?)";
		PreparedStatement pstmt = null;
		isSuccess = false;
		try {
			conn = DBHelper.getConnection();
			pstmt = conn.prepareStatement(sql);
			pstmt.setString(1, user.getUserName());
			pstmt.setString(2, user.getPassword());
			pstmt.setString(3, user.getCnname());
			pstmt.setString(4, user.getCategory());
			pstmt.setString(5, user.getDeptName());
			pstmt.setString(6, user.getEmail());
			pstmt.setString(7, user.getStatus());
			pstmt.setDate(8, new java.sql.Date(user.getRegTime().getTime()));
			pstmt.setString(9, user.getAdminName());
			int i = pstmt.executeUpdate();
			if(i > 0) {
				isSuccess = true;
			}
			
			return isSuccess;
		} catch (SQLException e) {
			e.printStackTrace();
			return isSuccess;
		} finally {
			pstmt.close();
		}
	}
	
	// 删除用户
	public boolean delUserByName(String userName) throws SQLException {
		Connection conn = null;
		String sql = "delete from users where userName = ?";
		PreparedStatement pstmt = null;
		isSuccess = false;
	
		try {
			conn = DBHelper.getConnection();
			pstmt = conn.prepareStatement(sql);
			pstmt.setString(1, userName);
			int i = pstmt.executeUpdate();
			
			if(i > 0) {
				isSuccess = true;
			}
			
			return isSuccess;
		} catch (SQLException e) {
			e.printStackTrace();
			return isSuccess;
		} finally {
			pstmt.close();
		}
	}
	
	// 编辑用户
	public boolean editUser(User user) throws SQLException {
		Connection conn = null;
		String sql = "update users set password=?,cnName=?,category=?,deptName=?,email=?,adminName=? where userName=?";
		PreparedStatement pstmt = null;
		isSuccess = false;
		
		try {
			conn = DBHelper.getConnection();
			pstmt = conn.prepareStatement(sql);
			pstmt.setString(1, user.getPassword());
			pstmt.setString(2, user.getCnname());
			pstmt.setString(3, user.getCategory());
			pstmt.setString(4, user.getDeptName());
			pstmt.setString(5, user.getEmail());
			// pstmt.setString(6, user.getStatus());
			pstmt.setString(6, user.getAdminName());
			pstmt.setString(7, user.getUserName());
			int i = pstmt.executeUpdate();
			
			if(i > 0) {
				isSuccess = true;
			}
			
			return isSuccess;
		} catch (SQLException e) {
			e.printStackTrace();
			return isSuccess;
		} finally {
			pstmt.close();
		}
	}
	
	// 审批用户
	public boolean approveUser(String userName) throws SQLException {
		Connection conn = null;
		String sql = "update users set status='正常' where userName=?";
		PreparedStatement pstmt = null;
		isSuccess = false;
		
		try {
			conn = DBHelper.getConnection();
			pstmt = conn.prepareStatement(sql);
			pstmt.setString(1, userName);
			int i = pstmt.executeUpdate();
			
			if(i > 0) {
				isSuccess = true;
			}
			
			return isSuccess;
		} catch (SQLException e) {
			e.printStackTrace();
			return isSuccess;
		} finally {
			pstmt.close();
		}
	}
	
	// 查询用户许可请求表
	public List<UserLicenseReq> queryUserLicenseReq(String deptName, String userName, String softName, String status, String startDate, String endDate) throws SQLException {
		StringBuffer sql = new StringBuffer("select * from userLicenseReq where 1=1");
		if(!StringUtils.isEmpty(deptName)) {
			sql.append(" and deptName = '").append(deptName).append("'");
		}
		if(!StringUtils.isEmpty(userName)) {
			sql.append(" and userName like '%").append(userName).append("%'");
		}
		if(!StringUtils.isEmpty(softName)) {
			sql.append(" and softName = '").append(softName).append("'");
		}
		if(!StringUtils.isEmpty(status)) {
			sql.append(" and status = '").append(status).append("'");
		}
//		if(!StringUtils.isEmpty(dateSearch)) {
//			sql.append(" and DATE_SUB(CURDATE(), INTERVAL ").append(dateSearch).append(" DAY) <= date(reqDate)");
//		}
//		if(!StringUtils.isEmpty(startDate) && !StringUtils.isEmpty(endDate)) {
//			sql.append(" and reqDate between '").append(startDate).append("' and '").append(endDate).append("'");
//		} else {
//			sql.append(" and reqDate > CAST((CAST(SYSDATE() AS DATE)) AS DATETIME)");
//		}

		Connection conn = null;
		PreparedStatement pstmt = null;
		ResultSet rs = null;
		List<UserLicenseReq> list = null;
		UserLicenseReq userLicenseReq = null;
		
		try {
			conn = DBHelper.getConnection();
			pstmt = conn.prepareStatement(sql.toString());
			rs = pstmt.executeQuery();
			list = new ArrayList<UserLicenseReq>();
			
			while (rs.next()) {
				userLicenseReq = new UserLicenseReq();
				userLicenseReq.setAdminName(rs.getString("adminName"));
				userLicenseReq.setAssignDate(rs.getDate("assignDate"));
				userLicenseReq.setAssignNum(rs.getInt("assignNum"));
				userLicenseReq.setDeptName(rs.getString("deptName"));
				userLicenseReq.setReason(rs.getString("reason"));
				userLicenseReq.setReqDate(rs.getDate("reqDate"));
				userLicenseReq.setReqId(rs.getInt("reqId"));
				userLicenseReq.setReqQuantity(rs.getInt("reqQuantity"));
				userLicenseReq.setStatus(rs.getString("status"));
				userLicenseReq.setUserName(rs.getString("userName"));
				userLicenseReq.setSoftName(rs.getString("softName"));
				
				list.add(userLicenseReq);
			}
			return list;
			
		} catch (SQLException e) {
			e.printStackTrace();
			return list;
		} finally {
			pstmt.close();
		}
	}
	
	// 查询用户许可表
	public List<UserLicense> queryUserLicense(String userName, String softName, String deptName) throws SQLException {
		Connection conn = null;
		StringBuffer sql = new StringBuffer("select * from userLicense where 1=1");
		if(!StringUtils.isEmpty(userName)) {
			sql.append(" and userName like '%").append(userName).append("%'");
		}
		if(!StringUtils.isEmpty(softName)) {
			sql.append(" and softName = '").append(softName).append("'");
		}
		if(!StringUtils.isEmpty(deptName)) {
			sql.append(" and deptName = '").append(deptName).append("'");
		}
		PreparedStatement pstmt = null;
		ResultSet rs = null;
		List<UserLicense> list = null;
		UserLicense userLicense = null;
		
		try {
			conn = DBHelper.getConnection();
			pstmt = conn.prepareStatement(sql.toString());
			rs = pstmt.executeQuery();
			list = new ArrayList<UserLicense>();
			
			while(rs.next()) {
				userLicense = new UserLicense();
				userLicense.setActivated(rs.getInt("activated"));
				userLicense.setAssigned(rs.getInt("assigned"));
				userLicense.setDeptName(rs.getString("deptName"));
				userLicense.setId(rs.getInt("id"));
				userLicense.setSoftName(rs.getString("softName"));
				userLicense.setUserName(rs.getString("userName"));
				
				list.add(userLicense);
			}
			
			return list;
		} catch (SQLException e) {
			e.printStackTrace();
			return list;
		} finally {
			pstmt.close();
		}
		
	}
	
	// 查询用户激活软件情况
	public List<UserActiveSoft> queryUserActiveSoft(String userName, String softName, String status, String deptName) throws SQLException {
		Connection conn = null;
		StringBuffer sql = new StringBuffer("select a.* from userActSoft a");
		sql.append(" left join users b on a.userName = b.userName");
		sql.append(" where 1=1");
		if(!StringUtils.isEmpty(userName)) {
			sql.append(" and a.userName like '%").append(userName).append("%'");
		}
		if(!StringUtils.isEmpty(softName)) {
			sql.append(" and a.softName = '").append(softName).append("'");
		}
		if(!StringUtils.isEmpty(status)) {
			sql.append(" and a.status = '").append(status).append("'");
		}
		if(!StringUtils.isEmpty(deptName)) {
			sql.append(" and b.deptName = '").append(deptName).append("'");
		}
		PreparedStatement pstmt = null;
		ResultSet rs = null;
		List<UserActiveSoft> list = null;
		UserActiveSoft userActiveSoft = null;
		
		try {
			conn = DBHelper.getConnection();
			pstmt = conn.prepareStatement(sql.toString());
			rs = pstmt.executeQuery();
			list = new ArrayList<UserActiveSoft>();
			
			while(rs.next()) {
				userActiveSoft = new UserActiveSoft();
				userActiveSoft.setId(rs.getInt("id"));
				userActiveSoft.setExpireDate(rs.getDate("expireDate"));
				userActiveSoft.setIp(rs.getString("ip"));
				userActiveSoft.setMAC(rs.getString("MAC"));
				userActiveSoft.setMachineId(rs.getString("machineId"));
				userActiveSoft.setOs(rs.getString("os"));
				userActiveSoft.setReqDate(rs.getDate("reqDate"));
				userActiveSoft.setSoftName(rs.getString("softName"));
				userActiveSoft.setStatus(rs.getString("status"));
				userActiveSoft.setUserName(rs.getString("userName"));
				
				list.add(userActiveSoft);
			}
			
			return list;
		} catch (SQLException e) {
			e.printStackTrace();
			return list;
		} finally {
			pstmt.close();
		}
	}
	
	// 查询用户激活记录情况
	public List<UserActRecord> queryUserActRecord(String softName, String dateSearch, String status) throws SQLException {
		StringBuffer sql = new StringBuffer("select * from userActRecord where 1=1");
		if(!StringUtils.isEmpty(status)) {
			sql.append(" and status = '").append(status).append("'");
		}
		if(!StringUtils.isEmpty(softName)) {
			sql.append(" and softName = '").append(softName).append("'");
		}
		if(!StringUtils.isEmpty(dateSearch)) {
			sql.append(" and DATE_SUB(CURDATE(), INTERVAL ").append(dateSearch).append(" DAY) <= date(reqDate)");
		}
		
		Connection conn = null;
		PreparedStatement pstmt = null;
		ResultSet rs = null;
		List<UserActRecord> list = null;
		UserActRecord userActRecord = null;
		
		try {
			conn = DBHelper.getConnection();
			pstmt = conn.prepareStatement(sql.toString());
			rs = pstmt.executeQuery();
			list = new ArrayList<UserActRecord>();
			
			while(rs.next()) {
				userActRecord = new UserActRecord();
				userActRecord.setId(rs.getInt("id"));
				userActRecord.setExpireDate(rs.getDate("expireDate"));
				userActRecord.setIp(rs.getString("ip"));
				userActRecord.setMAC(rs.getString("MAC"));
				userActRecord.setMachineId(rs.getString("machineId"));
				userActRecord.setOs(rs.getString("os"));
				userActRecord.setReqDate(rs.getDate("reqDate"));
				userActRecord.setSoftName(rs.getString("softName"));
				userActRecord.setStatus(rs.getString("status"));
				userActRecord.setUserName(rs.getString("userName"));
				userActRecord.setErrCode(rs.getString("errCode"));
				
				list.add(userActRecord);
			}
			
			return list;
		} catch (SQLException e) {
			e.printStackTrace();
			return list;
		} finally {
			pstmt.close();
		}
	}
	
	// 查询用户记录表软件激活成功和失败的次数
	public Map<String, Map<String, String>> queryRecordAmount(String softName, String startDate, String endDate) throws SQLException {
		StringBuffer sql = new StringBuffer();
		sql.append("select softname,status,count(status) amount from userActrecord where 1=1");
		if(!StringUtils.isEmpty(softName)) {
			sql.append(" and softName ='").append(softName).append("'");
		}
		if(!StringUtils.isEmpty(startDate) && !StringUtils.isEmpty(endDate)) {
			sql.append(" and reqDate between '").append(startDate).append("' and '").append(endDate).append("'");
		} else {
			sql.append(" and reqDate > CAST((CAST(SYSDATE() AS DATE)) AS DATETIME)");
		}
		sql.append(" group by softname,status");

		System.out.println(sql);

		Connection conn = null;
		PreparedStatement pstmt = null;
		ResultSet rs = null;
		Map<String, Map<String, String>> mapm = null;
		Map<String, String> map= null;
		
		try {
			conn = DBHelper.getConnection();
			pstmt = conn.prepareStatement(sql.toString());
			rs = pstmt.executeQuery();
			mapm = new HashMap<String, Map<String, String>>();
			
			while(rs.next()) {
				if(mapm.get(rs.getString("softName")) == null) {
					map = new HashMap<String, String>();
				}
				
				map.put(rs.getString("status"), rs.getString("amount"));				
				mapm.put(rs.getString("softName"), map);
			}
			return mapm;
		} catch (SQLException e) {
			e.printStackTrace();
			return mapm;
		} finally {
			pstmt.close();
		}
	}
	
	// 激活错误类型统计
	public Map<String, String > queryErrCode(String startDate, String endDate) throws SQLException {
		StringBuffer sql = new StringBuffer();
		sql.append("select errCode,count(errCode) amount");
		sql.append(" from userActrecord");
		sql.append(" where (errCode is not null and errCode <> '')");
//		if(!StringUtils.isEmpty(dateSearch)) {
//			sql.append(" and DATE_SUB(CURDATE(), INTERVAL ").append(dateSearch).append(" DAY) <= date(reqDate)");
//		}
		if(!StringUtils.isEmpty(startDate) && !StringUtils.isEmpty(endDate)) {
			sql.append(" and reqDate between '").append(startDate).append("' and '").append(endDate).append("'");
		} else {
			sql.append(" and reqDate > CAST((CAST(SYSDATE() AS DATE)) AS DATETIME)");
		}
		sql.append(" group by errCode");
		
		Connection conn = null;
		PreparedStatement pstmt = null;
		ResultSet rs = null;
		Map<String, String> map= null;
		
		try {
			conn = DBHelper.getConnection();
			pstmt = conn.prepareStatement(sql.toString());
			rs = pstmt.executeQuery();
			map = new HashMap<String, String>();
			
			while(rs.next()) {
				map.put(rs.getString("errCode"), rs.getString("amount"));
			}
			return map;
		} catch (SQLException e) {
			e.printStackTrace();
			return map;
		} finally {
			pstmt.close();
		}
	}
	
	// 批准许可数量
	public boolean editAssignNum(int assignNum, String adminNmae, String status, int reqId) throws SQLException {
		Connection conn = null;
		StringBuffer sql = new StringBuffer("update userLicenseReq set assignNum=?,adminName=?,status=?,assignDate=? where reqId=?");
		PreparedStatement pstmt = null;
		
		try {
			conn = DBHelper.getConnection();
			pstmt = conn.prepareStatement(sql.toString());
			pstmt.setInt(1, assignNum);
			pstmt.setString(2, adminNmae);
			pstmt.setString(3, status);
			pstmt.setDate(4, new java.sql.Date(new Date().getTime()));
			pstmt.setInt(5, reqId);
			
			int i = pstmt.executeUpdate();
			if(i > 0) {
				isSuccess = true;
			}
			
			return isSuccess;
		} catch (SQLException e) {
			e.printStackTrace();
			return isSuccess;
		} finally {
			pstmt.close();
		}
		
	}
	
	// 查询用户
	public List<User> queryUsers(List<Map<String, String>> conditionList) {
		StringBuffer sql = new StringBuffer("select * from users where 1=1");
		String startDate = null;
		String endDate = null;
		if(conditionList != null && conditionList.size() > 0) {
			for (Map<String, String> map : conditionList) {
				String key = map.get("属性");
				if(StringUtils.equals(key, "startDate")) {
					startDate = map.get("值");
				} else if(StringUtils.equals(key, "endDate")) {
					endDate = map.get("值");
				} else {
					sql.append(" and ").append(map.get("属性")).append(" like '%").append(map.get("值")).append("%'");
				}
			}
		}
		if(!StringUtils.isEmpty(startDate) && !StringUtils.isEmpty(endDate)) {
			sql.append(" and regTime between '").append(startDate).append("' and '").append(endDate).append("'");
		} else {
			// sql.append(" and regTime > CAST((CAST(SYSDATE() AS DATE)  - INTERVAL 8 DAY) AS DATETIME)");
		}
		Connection conn = null;
		Statement stmt = null;
		ResultSet rs = null;
		List<User> list = new ArrayList<User>();
		User user = null;
		try {
			conn = DBHelper.getConnection();
			stmt = (Statement) conn.createStatement();
			rs = stmt.executeQuery(sql.toString());
			while(rs.next()) {
				user = new User();
				user.setActTimes(rs.getInt("actTimes"));
				user.setAdminName(rs.getString("adminName"));
				user.setCategory(rs.getString("category"));
				user.setCnname(rs.getString("cnname"));
				user.setDeptName(rs.getString("deptName"));
				user.setEmail(rs.getString("email"));
				user.setLoginTimes(rs.getInt("loginTimes"));
				user.setPassword(rs.getString("password"));
				user.setRegTime(rs.getDate("regTime"));
				user.setRequestTimes(rs.getInt("requestTimes"));
				user.setStatus(rs.getString("status"));
				user.setUserName(rs.getString("username"));
				
				list.add(user);
			}
			stmt.close();
			rs.close();
			
			return list;
		} catch (SQLException e) {
			e.printStackTrace();
			return list;
		}
	}
	
	// 查询一个用户
	public User queryOneUser(String userName, String password) {
		String sql = "select * from users where userName=? and password=?";
		PreparedStatement pstmt = null;
		User user = null;
		Connection conn = null;
		ResultSet rs = null;
		
		try {
			conn = DBHelper.getConnection();
			pstmt = conn.prepareStatement(sql);
			pstmt.setString(1, userName);
			pstmt.setString(2, password);
			
			rs = pstmt.executeQuery();
			
			if(rs.next()) {
				user = new User();
				user.setActTimes(rs.getInt("actTimes"));
				user.setAdminName(rs.getString("adminName"));
				user.setCategory(rs.getString("category"));
				user.setCnname(rs.getString("cnname"));
				user.setDeptName(rs.getString("deptName"));
				user.setEmail(rs.getString("email"));
				user.setLoginTimes(rs.getInt("loginTimes"));
				user.setPassword(rs.getString("password"));
				user.setRegTime(rs.getDate("regTime"));
				user.setRequestTimes(rs.getInt("requestTimes"));
				user.setStatus(rs.getString("status"));
				user.setUserName(rs.getString("username"));
			}

			pstmt.close();
			rs.close();

			return user;
		} catch (SQLException e) {
			e.printStackTrace();
			return user;
		}
	}

	// 状态切换
	public boolean editUserStatus(String status, String name) throws SQLException {
		String sql = "update users set status=? where userName=?";
		Connection conn = null;
		PreparedStatement pstmt = null;

		try {
			conn = DBHelper.getConnection();
			pstmt = conn.prepareStatement(sql);
			pstmt.setString(1, status);
			pstmt.setString(2, name);

			int i = pstmt.executeUpdate();

			if(i > 0) {
				return  true;
			}

			return false;
		} catch (SQLException e) {
			e.printStackTrace();
			return  false;
		} finally {
			pstmt.close();
		}

	}

	public UserDao() {
	}

}